package com.ld.shieldsb.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

import com.ld.shieldsb.annotation.dao.DB;
import com.ld.shieldsb.annotation.field.db.Id;
import com.ld.shieldsb.annotation.field.db.SEQ;
import com.ld.shieldsb.annotation.util.AnnotationUtil;
import com.ld.shieldsb.common.core.reflect.FunctionUtil;
import com.ld.shieldsb.common.core.reflect.FunctionUtil.Property;
import com.ld.shieldsb.dao.model.JoinModel;
import com.ld.shieldsb.dao.model.PageNavigationBean;
import com.ld.shieldsb.dao.model.QueryModel;
import com.ld.shieldsb.dao.util.LogUtil;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class OracleBaseDao extends TransactionImplBaseDao {

    public OracleBaseDao() {
        this(DataKeyStatic.CREDIT_ORACLE_DATA_SOURE_KEY);
    }

    public OracleBaseDao(String key) {
        super(key);
    }

    /**
     * 
     * 判断某个id是否在数据库存在
     * 
     * @Title exists
     * @param id
     * @return boolean
     */
    public <T> boolean exists(Class<T> classOfT, Object id) {
        return exists(classOfT, "id", id);
    }

    /**
     * 
     * 判断某个字段值是否存在
     * 
     * @Title exists
     * @param key
     *            字段
     * @param value
     *            字段值
     * @return boolean
     */
    public <T> boolean exists(Class<T> classOfT, String key, Object value) {
        String tableName = getTableName(classOfT);
        return getCount("select 1 from " + tableName + " where rownum=1 and " + key + "=?", value) > 0;
    }

    /**
     * 
     * 根据UUID获取数据
     * 
     * @Title findByUuid
     * @param uuid
     *            UUID
     * @return T
     */
    public <T> T findByUuid(Class<T> classOfT, String uuid) {
        String tableName = getTableName(classOfT);
        return getOne(classOfT, "select * from " + tableName + " where rownum=1 and uuid=?", uuid);
    }

    /**
     * 
     * 根据主键id获取数据
     * 
     * @Title findById
     * @param id
     *            主键id
     * @return T
     */
    public <T> T findById(Class<T> classOfT, Object id) {
        return findById(classOfT, "id", id);
    }

    /**
     * 
     * 根据某个字段值获取数据
     * 
     * @Title findById
     * @param key
     *            字段
     * @param value
     *            字段值
     * @return T
     */
    public <T> T findById(Class<T> classOfT, String key, Object value) {
        String tableName = getTableName(classOfT);
        return getOne(classOfT, "select * from " + tableName + " where rownum=1 and " + key + "=?", value);
    }

    protected int updateThrowException(String sql, Object... params) throws SQLException {
        if (canSave) {
            Connection connection = getCon();
            try {
                LogUtil.daoDebug("sql: " + sql + " params: " + Arrays.asList(params));
                return QRY_RUN.update(connection, sql, params);
            } finally {
                if (connection.getAutoCommit()) {
                    closeCon(connection);
                }
            }
        } else {
            LogUtil.daoDebug("canSave=false不执行保存 sql: " + sql + " params: " + Arrays.asList(params));
            return 0;
        }
    }

    /**
     * 
     * 根据查询条件获取map
     * 
     * @Title getMap
     * @param queryModel
     *            查询条件
     * @return Map<String,Object>
     */
    public <T> Map<String, Object> getMap(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT t.* FROM (select " + queryModel.getSelectFields() + " from " + tableName + " " + queryModel.getOrderQueryStr()
                + ") t WHERE rownum=1";
        return getMap(sql, queryModel.getParams());
    }

    /**
     * 
     * 获取某个实体MODEL
     * 
     * @Title getOne
     * @param queryModel
     *            查询条件
     * @return T
     */
    public <T> T getOne(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT t.* FROM (SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr()
                + ") t WHERE rownum=1";
        return getOne(classOfT, sql, queryModel.getParams());
    }

    /**
     * 
     * 根据查询条件获取具体条数的LIST
     * 
     * @Title getList
     * @param queryModel
     *            查询条件
     * @param size
     *            查询条数
     * @return List<T>
     */
    public <T> List<T> getList(Class<T> classOfT, QueryModel queryModel, int size) {
        String tableName = getTableName(classOfT);
        String sql = "select * from (select " + queryModel.getSelectFields() + " from " + tableName + " " + queryModel.getOrderQueryStr()
                + ") where rownum<=" + size;
        return getList(classOfT, sql, queryModel.getParams());
    }

    /**
     * 
     * 根据sql条件获取前几条数据
     * 
     * @Title getTopList
     * @param sql
     *            sql条件
     * @param rownum
     *            条数
     * @return List<T>
     */
    public <T> List<T> getTopList(Class<T> classOfT, String sql, int rownum) {
        String pageSql = "select * from (" + sql + ") where rownum<= ?";
        return super.getList(classOfT, pageSql, rownum);
    }

    /**
     * 
     * 根据查询条件返回分页数据
     * 
     * @Title getPageNavigationBean
     * @param queryModel
     *            查询条件
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize) {
        return getPageNavigationBean(classOfT, queryModel, pageNum, pageSize, true);
    }

    /**
     * 
     * 根据查询条件返回分页数据,按照公布日期排序再按照创建时间排序
     * 
     * @Title getPageNavigationBeanOrder
     * @param queryModel
     *            查询条件
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBeanOrder(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<T>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);

        String tableName = getTableName(classOfT);
        String countSql = "select count(1) from " + tableName + " " + queryModel.getNoOrderQueryStr();
        int count = getCount(countSql, queryModel.getParams());
        pageBean.setTotalCount(count);
        int startRowNum = pageBean.getCurrentPoint();
        int endRowNum = startRowNum + pageSize;
        String pageBeanSql = "select " + queryModel.getSelectFields() + " from " + tableName;
        pageBeanSql += " " + queryModel.getOrderQueryStr() + " ,createTime desc";
        pageBeanSql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + pageBeanSql + ") TT WHERE ROWNUM <" + endRowNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startRowNum;
        List<T> list = getList(classOfT, pageBeanSql, queryModel.getParams());
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 自定义查询语句的分页（select语句，不带分页信息）
     * 
     * @Title getPageNavigationBean
     * @author 吕凯
     * @date 2019年8月15日 上午8:11:22
     * @param classOfT
     * @param sql
     *            select查询语句不带分页
     * @param pageNum
     * @param pageSize
     * @param params
     * @return PageNavigationBean<T>
     */
    protected <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, String sql, int pageNum, int pageSize, Object... params) {
        sql = sql.trim(); // 去除首尾空格
        PageNavigationBean<T> pageBean = new PageNavigationBean<T>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        Pattern CONDITION_PATTERN = Pattern.compile("^(?i)select\\s+(.+?)\\s+from"); // ^表示开头非中间 (?i)忽略大小写，将查询字段替换为select count(1) from
        Matcher m = CONDITION_PATTERN.matcher(sql);
        String countSql = sql;
        if (m.find()) {
            String valuesStr = m.group(0); // 代表整个符合条件的字符串
            countSql = countSql.replaceFirst(valuesStr, "SELECT COUNT(1) FROM");
        }
        int count = getCount(countSql, params);

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint();
        int endRowNum = startRowNum + pageSize;

        sql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + sql + ") TT WHERE ROWNUM <" + endRowNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startRowNum;
        List<T> list = getList(classOfT, sql, params);
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 自定义count和select sql语句的分页查询
     * 
     * @Title getPageNavigationBean
     * @author 吕凯
     * @date 2019年8月15日 上午8:10:06
     * @param classOfT
     * @param countSql
     *            count语句
     * @param selectSql
     *            select语句不带分页
     * @param pageNum
     *            页数
     * @param pageSize
     *            每页条数
     * @param params
     * @return PageNavigationBean<T>
     */
    protected <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, String countSql, String selectSql, int pageNum,
            int pageSize, Object... params) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<T>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        int count = getCount(countSql, params);

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint();
        int endRowNum = startRowNum + pageSize;

        selectSql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + selectSql + ") TT WHERE ROWNUM <" + endRowNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startRowNum;
        List<T> list = getList(classOfT, selectSql, params);
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 
     * 返回不带排序的分页数据
     * 
     * @Title getPageNavigationBeanNoOrder
     * @param queryModel
     *            查询条件
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBeanNoOrder(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize) {
        return getPageNavigationBean(classOfT, queryModel, pageNum, pageSize, false);
    }

    private <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize,
            boolean isOrder) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<T>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        String tableName = getTableName(classOfT);
        String countSql = "select count(1) from " + tableName + " " + queryModel.getNoOrderQueryStr();

        int count = getCount(countSql, queryModel.getParams());

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint();
        int endRowNum = startRowNum + pageSize;

        String pageBeanSql = "select " + queryModel.getSelectFields() + " from " + tableName;
        if (isOrder) {
            pageBeanSql += " " + queryModel.getOrderQueryStr();
        } else {
            pageBeanSql += " " + queryModel.getNoOrderQueryStr();
        }

        pageBeanSql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + pageBeanSql + ") TT WHERE ROWNUM <" + endRowNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startRowNum;
        List<T> list = getList(classOfT, pageBeanSql, queryModel.getParams());
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 初始化joinModel
     * 
     * @Title initJoin
     * @author 吕凯
     * @date 2019年8月15日 下午2:37:40
     * @param classOfT
     * @return JoinModel
     */
    public <T> OracleBaseDao initJoin(Class<T> classOfT, QueryModel queryModel) {
        JoinModel model = new JoinModel(classOfT, queryModel);
        setJoinModel(model);
        return this;
    }

    /**
     * jion操作，只join一级关联的表，如一对一，多对多的对象和中间表
     * 
     * @Title join
     * @author 吕凯
     * @date 2019年8月16日 下午4:49:52
     * @param property
     * @param queryModel
     * @return
     * @throws Exception
     *             MySqlBaseDao
     */
    public <T> OracleBaseDao join(Property<T, ?> property, QueryModel queryModel) throws Exception {
        return join(property, queryModel, null);
    }

    /**
     * 用于join多对多
     * 
     * @Title join
     * @author 吕凯
     * @date 2019年8月16日 下午4:51:14
     * @param property
     * @param middleQueryModel
     * @param queryModel
     * @return
     * @throws Exception
     *             MySqlBaseDao
     */
    public <T> OracleBaseDao join(Property<T, ?> property, QueryModel middleQueryModel, QueryModel otherQueryModel) throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        String fieldName = FunctionUtil.getFunctionName(property);
        Field field = AnnotationUtil.getField(joinModel.getMainClass(), fieldName);
        if (otherQueryModel == null) {
            joinModel.addLinkedTable(field, middleQueryModel);
        } else {
            joinModel.addLinkedTable(field, middleQueryModel, otherQueryModel); // 添加
        }
        return this;
    }

    @SuppressWarnings("unchecked")
    public <T> T getOneByJoin() throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        T obj = (T) getOne(joinModel.getMainClass(), joinModel.getSql(), joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return obj;
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> getListByJoin() throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        List<T> list = (List<T>) getList(joinModel.getMainClass(), joinModel.getSql(), joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return list;
    }

    @SuppressWarnings("unchecked")
    public <T> PageNavigationBean<T> getPageNavigationBeanByJoin(int pageNum, int pageSize) throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        PageNavigationBean<T> pageBean = (PageNavigationBean<T>) getPageNavigationBean(joinModel.getMainClass(), joinModel.getSql(),
                pageNum, pageSize, joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return pageBean;
    }

    /**
     * 
     * 返回分页数据,不区分model
     * 
     * @Title getPageNavigationBean
     * @param classOfT
     * @param queryModel
     *            条件
     * @param startNum
     *
     * @param endNum
     *
     * @return PageNavigationBean<TT>
     */
    public <T> List<T> getList(Class<T> classOfT, QueryModel queryModel, int startNum, int endNum) {
        String tableName = getTableName(classOfT);
        String pageBeanSql = "select " + queryModel.getSelectFields() + " from " + tableName + " " + queryModel.getOrderQueryStr();
        pageBeanSql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + pageBeanSql + ") TT WHERE ROWNUM <" + endNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startNum;

        return getList(classOfT, pageBeanSql, queryModel.getParams());
    }

    /**
     * 
     * 根据条件删除数据
     * 
     * @Title batchDelete
     * @param queryModel
     *            删除条件
     * @return int
     */
    public <T> int batchDelete(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "delete from " + tableName + " " + queryModel.getOrderQueryStr();
        return update(sql, queryModel.getParams());
    }

    /**
     * 
     * 根据UUID更新数据
     * 
     * @Title updateByUuid
     * @param uuid
     *            uuid
     * @param column_name
     *            字段名
     * @param newValue
     *            字段值
     * @return int
     */
    public <T> int updateByUuid(Class<T> classOfT, Object uuid, String column_name, String newValue) {
        String tableName = getTableName(classOfT);
        String sql = "update " + tableName + " set " + column_name + " = ? where uuid = ?";
        return update(sql, newValue, uuid);
    }

    /**
     * 
     * 更新2个字段
     * 
     * @Title updateDoubleContentByUuid
     * @param uuid
     * @param column1
     *            字段1名
     * @param column1_content
     *            字段1值
     * @param column2
     *            字段2名
     * @param column2_content
     *            字段2值
     * @return int
     */
    public <T> int updateDoubleContentByUuid(Class<T> classOfT, Object uuid, String column1, String column1_content, String column2,
            String column2_content) {
        String tableName = getTableName(classOfT);
        String sql = "update " + tableName + " set " + column1 + " = ? , " + column2 + " = ? where uuid = ?";
        return update(sql, column1_content, column2_content, uuid);

    }

    /**
     * 获取下一个可用的主键
     * 
     * @Title getNextIdLong
     * @author 吕凯
     * @date 2017年11月21日 上午11:24:30
     * @param classOfT
     * @return
     * @see com.ld.shieldsb.dao.TransactionDao#getNextIdLong(java.lang.Class)
     */
    @Override
    public <T> long getNextIdLong(Class<T> classOfT) {
        long flag = 0;
        Field[] fields = classOfT.getDeclaredFields();
        for (Field field : fields) {
            Id id = field.getAnnotation(Id.class);
            if (id != null) {
                if (id.db() == DB.ORACLE) {
                    String seq = id.seq();
                    if (StringUtils.isNotBlank(seq)) {
                        return getLong("SELECT " + seq + ".NEXTVAL FROM dual ");
                    } else {
                        log.error("注解Id的seq属性未定义");
                    }
                }
            }
        }
        return flag;
    }

    /**
     * 
     * 获取下一个可用的序列值
     * 
     * @Title getNextIdLong
     * @author 吕凯
     * @date 2018年1月5日 上午9:41:41
     * @param classOfT
     * @return
     * @see com.ld.shieldsb.dao.TransactionDao#getNextIdLong(java.lang.Class)
     */
    @Override
    public <T> long getNextSeqValue(Class<T> classOfT, String fieldName) {
        long flag = 0;
        Field field = AnnotationUtil.getField(classOfT, fieldName);
        if (field != null) {
            SEQ id = field.getAnnotation(SEQ.class);
            if (id != null) {
                if (id.db() == DB.ORACLE) {
                    String seq = id.seq();
                    if (StringUtils.isNotBlank(seq)) {
                        return getLong("SELECT " + seq + ".NEXTVAL FROM dual ");
                    } else {
                        log.error("注解SEQ的seq属性未定义");
                    }
                }
            }
        }
        return flag;
    }

    public <T> List<T> getListBySql(Class<T> classOfT, String sql, Object... params) {
        return getList(classOfT, sql, params);
    }

    public Map<String, Object> getMapSql(String sql, Object... params) {
        return getMap(sql, params);
    }

    /**
     *
     * 根据查询条件返回List<Map>
     *
     * @Title getMap
     * @param sql
     *            查询条件
     * @return Map<String,Object>
     */
    public <T> List<Map<String, Object>> getListMapSql(String sql, Object... params) {
        return getMapList(sql, params);
    }

    /**
     *
     * 根据查询sql返回 list数据
     *
     * @Title getListBySql
     * @param queryModel
     *            查询条件
     * @param condition
     *            SQL条件
     * @param size
     *            查询条数
     * @return List<T>
     */
    public <T> List<T> getListBySql(Class<T> classOfT, QueryModel queryModel, String condition, int size) {
        String tableName = getTableName(classOfT);
        String sql = "select * from (select " + queryModel.getSelectFields() + " from " + tableName + " where " + condition
                + ") where rownum<=" + size;
        return getList(classOfT, sql, queryModel.getParams());
    }

}
